﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Text.RegularExpressions;

namespace PPAP.DAO
{
    public class T_FileDAO
    {
        public T_FileDAO() { }

        /// <summary>
        /// 根据附件类型和id得到附件列表
        /// </summary>
        /// <param name="fileID"></param>
        /// <param name="types"></param>
        /// <returns></returns>
        public DataSet GetFileList(string fileID, int types)
        {
            string sql = "select F_ID,F_FileID,F_FileName,F_url,F_Detail,UpTime,UpBY,filesize,rownum from T_filetable where F_FileID='" + fileID + "' and F_Flag=" + types + " and isdelete=0";
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// 根据部件id得到附件列表
        /// </summary>
        /// <param name="fileID"></param>
        /// <param name="types"></param>
        /// <returns></returns>
        public DataSet GetFileList(string fileID)
        {
            string sql = "select t.*,rownum from (select F_ID,F_FileID,F_FileName,F_url,F_Detail,UpTime,UpBY,filesize,a.attachmenttype,F_Flag,a.sx from T_filetable t left join attachmenttype a on t.F_Flag=a.id where F_FileID='" + fileID + "'  and isdelete=0  order by sx,uptime)t";
            sql = @"select t.*,rownum,nvl(a.rid,0) rid from (select F_ID,F_FileID,F_FileName,F_url,F_Detail,UpTime,UpBY,filesize,a.attachmenttype,F_Flag,a.sx,a.id from T_filetable t left join attachmenttype a on t.F_Flag=a.id where F_FileID='" + fileID + @"'  and isdelete=0  order by sx,uptime)t
left join(select t.*,rownum rid from(select p.*,t.bdnttemplateavailable,t.uploadedfile from ATTACHMENTTYPE p left join ATTACHMENTTEMPLATE t on p.id=t.attachmenttypeid where    p.xs=1 order by sx) t)a
on t.id=a.id ";
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// 根据条件得到附件列表
        /// </summary>
        /// <param name="fileID"></param>
        /// <param name="types"></param>
        /// <returns></returns>
        public DataSet GetFileListForWhere(string sqlwhere,string sort)
        {
            string sql = string.Format("select F_ID,F_FileID,F_FileName,F_url,F_Detail,UpTime,UpBY,filesize,rownum,a.attachmenttype,F_Flag,a.sx,filetype from T_filetable t left join attachmenttype a on t.F_Flag=a.id where {0}  and isdelete=0 {1}", sqlwhere,sort);
            return DbHelperOra.Query(sql);
        }


        /// <summary>
        /// 根据id得到上传文件路径
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataSet Getpath(int id)
        {
            string sql = "select * from T_filetable where F_ID =" + id + "";
            return DbHelperOra.Query(sql);
        }
        /// <summary>
        /// 根据附件id删除对应附件信息
        /// </summary>
        /// <param name="F_ID"></param>
        /// <returns></returns>
        public bool DeleteFile(string F_ID)
        {
            string sql = "delete from T_filetable where F_ID='" + F_ID + "'";
            sql = "update t_filetable set isdelete=1 where F_ID='" + F_ID + "'";
            return DbHelperOra.ExecuteSql(sql) > 0;
        }
        /// <summary>
        /// 添加一条附件
        /// </summary>
        /// <param name="F_FileID"></param>
        /// <param name="F_Filename"></param>
        /// <param name="path"></param>
        /// <param name="types"></param>
        /// <returns></returns>
        public bool InsertFile(string F_FileID, string F_Filename, string path, int types, string url,string filesize,string remarks,string upby,string filetype,string pparid,string fileversion)
        {
            F_Filename = ReplaceSQLChar(F_Filename);
            path = ReplaceSQLChar(path);
            url = ReplaceSQLChar(url);
            remarks = ReplaceSQLChar(remarks);
            int F_ID = DbHelperOra.GetMaxID("F_ID", "t_filetable");
            string sql = @"insert into t_filetable(F_ID,F_FileID,F_Filename,F_Detail,F_Flag,F_url,UpTime,filesize,remarks,UPBY,filetype,pparno,fileversion,isdelete) values('" + F_ID + "','" + F_FileID + "','" + F_Filename + "','" + path + F_Filename + "'," + types + ",'" + url + "',(select sysdate from dual),'" + filesize + "','" + remarks + "','" + upby + "','"+filetype+"','"+pparid+"','"+fileversion+"',0)";
            return DbHelperOra.ExecuteSql(sql)>0;
        }

        /// <summary>
        /// 替换非法字符
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static string ReplaceSQLChar(string str)
        {
            if (str == String.Empty) return String.Empty;
            str = str.Replace("'", "‘");
            str = str.Replace(";", "；");
            str = str.Replace(",", ",");
            str = str.Replace("?", "?");
            str = str.Replace("<", "＜");
            str = str.Replace(">", "＞");
            str = str.Replace("(", "(");
            str = str.Replace(")", ")");
            str = str.Replace("@", "＠");
            str = str.Replace("=", "＝");
            str = str.Replace("+", "＋");
            str = str.Replace("*", "＊");
            str = str.Replace("&", "＆");
            str = str.Replace("#", "＃");
            str = str.Replace("%", "％");
            str = str.Replace("$", "￥");
            return str;
        }

        /// <summary>
        /// 过滤非法字符
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static string Filter(string value)
        {
            if (string.IsNullOrEmpty(value)) return string.Empty;
            value = Regex.Replace(value, @";", string.Empty);
            value = Regex.Replace(value, @"'", string.Empty);
            value = Regex.Replace(value, @"&", string.Empty);
            value = Regex.Replace(value, @"%20", string.Empty);
            value = Regex.Replace(value, @"--", string.Empty);
            value = Regex.Replace(value, @"==", string.Empty);
            value = Regex.Replace(value, @" <", string.Empty);
            value = Regex.Replace(value, @">", string.Empty);
            value = Regex.Replace(value, @"%", string.Empty);

            return value;
        }

        /// <summary>
        /// 文件管理
        /// </summary>
        /// <param name="sqlwhere"></param>
        /// <returns></returns>
        public DataSet GetListForSearch(string sqlwhere)
        {
            string sql = @"select rownum,t.* from (select a.attachmenttype,t.upby,UpTime,'' d1,'PPAR-'||p.pparno||'.'||PPARVERSION as pparno,r.bdntpartno,r.bdntpartname,r.color,t.filetype,filesize,'' d,F_url,F_FileName,t.isdelete from T_filetable t left join attachmenttype a on t.F_Flag=a.id 
                            left join ppar p on t.pparno=p.id 
                            left join part r on p.partid=r.id
                            where 1=1 and p.status!=9  ";
            sql += sqlwhere;
            sql += "  )t";
            return DbHelperOra.Query(sql);
        }
    }
}
